/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao

import entity.Sample;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author JAVIER
 */
class SamplesDAO implements DAO <Sample> {
        
    private Integer ID = 0;  
    
    private static final String SQL_INSERT = "INSERT INTO Samples ( serviceRequestid, name, quantity, radioactive, magnetic, state, photograficRecord, comments ) VALUES ( ?, ?,?,?, ? ,?,?, ?)";
    
    private static final String SQL_SELECT = " SELECT * FROM Samples where id= ?";
    
    private static final String SQL_SELECT_PROPERTY = " SELECT * FROM Samples where ? = ?" ;
    
    private static final String SQL_SELECT_All = " SELECT * FROM Samples";
    
    private static final String SQL_UPDATE = " UPDATE Samples SET id = ?, serviceRequestid= ?, name= ?, quantity= ?, radioactive= ?, magnetic= ?, state= ?, photograficRecord= ?, comments= ? WHERE id = ?";

    private static final String SQL_DELETE = " DELETE FROM Samples WHERE id = ?";
    
    //Ignore the error, Netbeans regards it like so but it's not.
    
    Integer create(Sample Smpl, Connection connect) 
        throws SQLException {

        PreparedStatement ps = null;
        try {
            ps = connect.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, Smpl.getServiceRequest().getId());
            ps.setString(2, Smpl.getName());
            ps.setFloat(3,Smpl.getQuantity());
            ps.setBoolean(4,Smpl.getRadioactive());
            ps.setBoolean(5,Smpl.getMagnetic());
            ps.setString(6,Smpl.getState());
            ps.setBoolean(7,Smpl.getPhotographicRecord());
            ps.setString(8,Smpl.getComments());
            ID = ps.executeUpdate();
        } finally {
            close(ps);
        }
        return ID;
        //TODO implement
    }
    
    Boolean remove(Sample Smpl, Connection connect) throws SQLException {
        Boolean result = false;
        PreparedStatement ps = null;
        try {
            ps = connect.prepareStatement(SQL_DELETE);
            ps.setInt(1, Smpl.getId());
            ps.executeUpdate();
            result = true;
        } finally {
            close(ps);
        }
        return result;
        //TODO implement
    }
    
    Boolean update(Sample Smpl, Connection connect) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = connect.prepareStatement(SQL_UPDATE, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, Smpl.getId());
            ps.setInt(2, Smpl.getServiceRequest().Id());
            ps.setString(3, Smpl.getName());
            ps.setFloat(4,Smpl.getQuantity());
            ps.setBoolean(5,Smpl.getRadioactive());
            ps.setBoolean(6,Smpl.getMagnetic());
            ps.setString(7,Smpl.getState());
            ps.setBoolean(8,Smpl.getPhotographicRecord());
            ps.setString(9,Smpl.getComments());
            ps.setInt(10, Smpl.getId());
            ID = ps.executeUpdate();
            
        } finally {
            close(ps);
        }
        return ID;
        //TODO implement
    }
    
    Sample find(Integer id, Connection connect) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connect.prepareStatement(SQL_SELECT);
            ps.setString(1, id);
            rs = ps.executeQuery();
            Sample samp = new Sample();
            result = null;
            samp = get_Sample(rs);
            return samp;
        } finally {
            close(rs);
            close(ps);  
        }
        //TODO implement
    }
    
    List findAll(Connection connect) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connect.prepareStatement(SQL_SELECT_All);
            rs = ps.executeQuery();
            List results = new ArrayList();
            Sample samp = new Sample();
            result = null;
            while (rs.next()) {
                samp = get_Sample(rs);
                results.add(samp);}
            return result;
        } finally {
            close(rs);
            close(ps);  
        }
        //TODO implement
    }
    
    List select_by_property(String camp, String Value, Connection connect)throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connect.prepareStatement(SQL_SELECT_PROPERTY);
            ps.setString(1, camp);
            ps.setString(2, value);
            rs = ps.executeQuery();
            List results = new ArrayList();
            Sample samp = new Sample();
            result = null;
            while (rs.next()) {
                samp = get_Sample(rs);
                results.add(samp);}
            return result;

        } finally {
            close(rs);
            close(ps);  
        }
        //TODO implement
    }
        
    Sample get_Sample (ResultSet rs) throws SQLException {
 
        Sample samp = new Sample();
        samp.setId(rs.getInt("id"));
        samp.setServiceRequest(rs.getInt("serviceRequest"));
        samp.setName(rs.getString("name"));
        samp.setQuantity(rs.getFloat("quantify"));
        samp.setRadioactive(rs.getBoolean("radioactiveType"));
        samp.setMagnetic(rs.getBoolean("magnetic"));
        samp.setState(rs.getString("state"));
        samp.setPhotographicRecord(rs.getBoolean("photograficRecord"));
        samp.setComments(rs.getString("comments"));
        
        return samp;
    }
}



